/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author And
 */
public class DBCustomer implements IFDBCustomer {
    private Connection con;

    public DBCustomer()
    {
        con = DbConnection.getInstance().getDBcon();
    }

    //Print all costumers.
    public ArrayList getAllCustomers(boolean retrieveAssociation)
    {
        ArrayList allCusObj = new ArrayList();
        allCusObj = miscWhere("", retrieveAssociation);
        return allCusObj;
    }
    //find customer ved hjælp af Fname, Lname, id eller phoneNo.
    public Customer findCustomerByFname(String fname, boolean retrieveAssociation)
    {
        String wClause = "fname = '"+ fname +"'";
        return singleWhere(wClause, retrieveAssociation);
    }
    public Customer findCustomerByLname(String lname, boolean retrieveAssociation)
    {
        String wClause = "lname = '"+ lname +"'";
        return singleWhere(wClause, retrieveAssociation);
    }
    public Customer findCustomerById(int id, boolean retrieveAssociation)
    {
        String wClause = "id = "+ id;
        return singleWhere(wClause, retrieveAssociation);
    }
    public Customer findCustomerByPhone(String phoneNo, boolean retrieveAssociation)
    {
        String wClause = "phoneNo = '"+ phoneNo +"'";
        return singleWhere(wClause, retrieveAssociation);
    }
    //Insert custumer.
    public int insertCustomer(Customer cos)
    {
        int nextId = GetMax.getMaxId("Select max(id) from Customer");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);

        int rc =-1;

        String query="INSERT INTO Customer(id, fname, lname, street, streetNo, phoneNo, email, zipCode) VALUES("+
        nextId + ",'"+
        cos.getFname() + "', '"+
        cos.getLname() + "', '"+
        cos.getStreet() +"', "+
        cos.getStreetNo() +",'"+
        cos.getPhoneNo() +"','"+
        cos.getEmail() + "',"+
        cos.getZipCode().getPostalCode()+ ")";
        System.out.println("insert: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Insert exception in Customer db: "+e);
        }
        return rc;
    }
    //Update Customer.
    public int updateCustomer(Customer cus)
    {
        Customer cusObj = cus;
        int rc = -1;

        String query = "UPDATE Customer SET " +
                "fname ='" + cusObj.getFname() + "', "+
                "lname ='" + cusObj.getLname() + "', "+
                "street ='" + cusObj.getStreet() + "', "+
                "streetNo =" + cusObj.getStreetNo() + ", "+
                "phoneNo ='" + cusObj.getPhoneNo() + "', "+
                "email ='" + cusObj.getEmail() + "' "+
                " WHERE id = " + cus.getId();
        System.out.println("Update query: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Update exception in Customer db: "+e);
        }
        return rc;
    }
    //Delete Customer.
    public int deleteCustomer(int id)
    {
        int rc = -1;
        String query="DELETE FROM Customer WHERE id = "+id;
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Delete exception in customer db: "+e);
        }
        return rc;
    }

    //SingleWhere method for finding one customer.
    private Customer singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        Customer cusObj = new Customer();
        cusObj.setId(0);
        String query = buildQuery(wClause);
        System.out.println(query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next())
            {
                cusObj = buildCostumer(results);
                stmt.close();

                if(retrieveAssociation)
                {
                    IFDBPostalCity dbPostObj = new DBPostalCity();
                    PostalCity postObj = dbPostObj.findCityZipCode(cusObj.getZipCode().getPostalCode());
                    System.out.println("Zip code is selected");
                    cusObj.setZipCode(postObj);
                }
                else
                {
                    cusObj.setZipCode(null);
                }
            }
        }
        catch(Exception e)
        {
            System.out.println("Query Exception; "+e);
            e.printStackTrace();
        }
        return cusObj;
    }

    //miscWhere method for finding more than one customer.
    private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Customer> list = new ArrayList();
        String query = buildQuery(wClause);

        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while(results.next())
            {
                Customer cusObj = new Customer();
                cusObj = buildCostumer(results);
                list.add(cusObj);
            }
            stmt.close();

            if(retrieveAssociation)
            {
                for(Customer cusObj : list)
                {
                    IFDBPostalCity dbPostObj = new DBPostalCity();
                    PostalCity postObj = dbPostObj.findCityZipCode(cusObj.getZipCode().getPostalCode());
                    System.out.println("Zipcode is selected");
                    cusObj.setZipCode(postObj);
                }
            }
        }
        catch(Exception e)
        {
            System.out.println("Queryexception" + e);
            e.printStackTrace();
        }
        return list;
    }


    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT id, fname, lname, street," +
                "streetNo, PhoneNo, email, zipCode" +
                " FROM Customer";

            if (wClause.length()>0)
                    query=query+" WHERE "+ wClause;

            return query;
    }

    //Method for building customer object.
    private Customer buildCostumer(ResultSet results)
    {
        Customer cusObj = new Customer();
        try
        {
            cusObj.setId(results.getInt(1));
            cusObj.setFname(results.getString(2));
            cusObj.setLname(results.getString(3));
            cusObj.setStreet(results.getString(4));
            cusObj.setStreetNo(results.getInt(5));
            cusObj.setPhoneNo(results.getString(6));
            cusObj.setEmail(results.getString(7));
            cusObj.setZipCode(new PostalCity(results.getInt(8)));

        }
        catch(Exception e)
        {
            System.out.println("Kunden kunne ikke oprettes");
        }
        return cusObj;
    }
}
